package com.mhkj.revise.plan.repository;

import java.util.List;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.mhkj.revise.plan.entity.PlanNode;
import com.mics.core.system.base.repository.BaseRepository;

/**
 * 计划节点
 * 
 * @author mics
 * @date 2018年7月6日
 * @version 1.0
 */
public interface PlanNodeRepository extends BaseRepository<PlanNode> {

	/**
	 * 根据节点编号查询计划节点
	 * 
	 * @author mics
	 * @date 2018年7月9日
	 * @version 1.0
	 */
	@Query("select t from PlanNode t where t.NO=:NO")
	PlanNode getPlanNodeByNO(@Param("NO") String NO);

	/**
	 * 查询关联业务系统
	 * @author mics
	 * @date 2018年7月12日
	 * @version 1.0
	 */
	@Query("select p from PlanNode p where p.busiModel.id != 0")
	List<PlanNode> queryBusiSysPlan();

	/**
	 * 计划统计数据
	 * @return
	 */
	@Query(value = "SELECT " + 
			"		tp.name," + 
			"		d.value," + 
			"		sum( tpn.over_day ) AS overDay," + 
			"		count( CASE WHEN tpn.over_day IS NOT NULL THEN 1 END ) AS overTasks," + 
			"		count( 1 ) AS tasks," + 
			"		count( CASE tpn.state WHEN '1' THEN 1 END ) AS ended," + 
			"		count( CASE tpn.state WHEN '0' THEN 1 END ) AS doing," + 
			"		count( CASE tpn.task_type_id WHEN (select id from t_dictionary where code='004') THEN 1 END ) AS important,tp.manager,"+ 
			"		count(DISTINCT tpn.manager_id) as managerNo " + 
			"	FROM" + 
			"		t_plan_node tpn,t_plan tp," + 
			"		t_dictionary d " + 
			"	WHERE" + 
			"		d.id = tpn.phase_id and tp.id=tpn.plan_id" + 
			"	GROUP BY" + 
			"	phase_id ", nativeQuery = true)
	List<Object> countList();

	

	/**
	 * 通过计划id查询计划节点集合 
	 * @param planId
	 * @return
	 */
	@Query("select t from PlanNode t where t.plan.id =:planId")
	List<PlanNode> findByPlanId(@Param("planId") Long planId);





	 
}
